package com.fdl.common.excel;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * Excel解析
 * 
 * @project: support
 * @Date:2010-11-10
 * @version 1.0
 * @Company: 33e9
 * @author hui.long
 */
public class ExcelParseScript<T> {

	private static final String XLS = "xls";

	// Log
	private static Log log = LogFactory.getLog(ExcelOperate.class);

	/**
	 * 数据模型
	 */
	protected T t;

	/**
	 * 需要读取的excel中sheet下标值
	 */
	private int sheetIndex;

	/**
	 * 解析excel工作表对象
	 */
	private Workbook wb;

	/**
	 * 工作表
	 */
	private Sheet sheet;

	/**
	 * 行对象
	 */
	private Row row;

	/**
	 * 单元格对象
	 */
	@SuppressWarnings("unused")
	private Cell cell;

	private static DecimalFormat df = new DecimalFormat("#.#");

	public void setSheetIndex(int sheetIndex) {
		this.sheetIndex = sheetIndex;
	}

	public void builExcel(InputStream inputStream, String fileType)
			throws IOException {
		if (XLS.equals(fileType)) {
			wb = new HSSFWorkbook(inputStream);
		} else {
			wb = new XSSFWorkbook(inputStream);
		}
	}
	public void builExcel(String fileType)
	throws IOException {
		if (XLS.equals(fileType)) {
			wb = new HSSFWorkbook();
		} else {
			wb = new XSSFWorkbook();
		}
	}

	/**
	 * 查询单个工作薄中的值，并返回
	 * 
	 * @param startRow
	 *            开始行数
	 * @param startCell
	 *            开始列数
	 * @param clazz
	 *            反射类
	 * @param fiedls
	 *            反射类属性数组
	 * @return
	 */
	public ArrayList<ArrayList<T>> getMuiltSheetCellValue(Integer startRow,
			Integer startCell, Class<T> clazz, String[] fields) {
		return getMuiltSheetCellValue(
				new Integer[][] { { startRow, startCell } }, clazz, fields);
	}

	/**
	 * 查询多个工作薄中的值，并返回
	 * 
	 * @param startRow
	 *            开始行数数组 (针对每个sheet)
	 * @param startCell
	 *            开始列数 (针对每行)
	 * @param clazz
	 *            反射类
	 * @param fiedls
	 *            反射类属性数组
	 * @return
	 */
	public ArrayList<ArrayList<T>> getMuiltSheetCellValue(
			Integer[][] startRowCell, Class<T> clazz, String[] fields) {

		// 保存表集合
		ArrayList<ArrayList<T>> tables = new ArrayList<ArrayList<T>>();

		// 保存行集合
		ArrayList<T> rows = null;

		// 获以多个工作薄
		for (int numSheet = 0; numSheet < wb.getNumberOfSheets(); numSheet++) {
			rows = new ArrayList<T>();
			// 获取其中一个工作薄
			sheet = wb.getSheetAt(numSheet);
			// 读取单个工作薄中的行
			for (int numRow = startRowCell[numSheet][0]; numRow <= sheet
					.getLastRowNum(); numRow++) {
				// 获取行
				row = sheet.getRow(numRow);
				// 设置数据
				rows.add(getRowToObj(row, startRowCell[numSheet][1], clazz,
						fields));
			}
			if (rows.size() > 0) {
				tables.add(rows);
			}
			rows = null;
		}
		return tables;
	}

	/**
	 * 将行数据转换为对象
	 * 
	 * @param <T>
	 * @param row
	 *            : 行对象
	 * @param clazz
	 *            : 转换类
	 * @param fiedls
	 *            : 属性名称集合
	 * @return
	 */
	public T getRowToObj(Row row, int startCellIndex, Class<T> clazz,
			String[] fields) {
		T t = null;
		try {
			t = clazz.newInstance();
		} catch (Exception e) {
			log.error(e.getMessage());
		}
		boolean flag = false;
		if (null != row) {
			// 读取行中单元格的值
			for (int numCell = startCellIndex; numCell <fields.length; numCell++) {
				final Object value = getCellValue(row, numCell);
				// 将值设置入对象中
				ExcelOperate.getFieldRtnVal(t, ConditionCollection.PREFIX_SET,
						fields[numCell], value);
				if (null != value && !"".equals(value)) {
					flag = true;
				}
			}
			flag = false;
		}
		return t;
	}

	/**
	 * 获取获取单元格值
	 * 
	 * @param row
	 *            excel行对象
	 * @param index
	 *            excel单元格的位置
	 * @return 返回获取单元格值
	 */
	protected static String getCellValue(Row row, int index) {
		return getCellValue(row, index, Integer.MAX_VALUE);
	}

	/**
	 * 获取获取单元格值
	 * 
	 * @param row
	 *            excel行对象
	 * @param index
	 *            excel单元格的位置
	 * @param maxLength单元格值最大长度
	 * @return 返回获取单元格值
	 */
	protected static String getCellValue(Row row, int index, int maxLength) {
		return getCellValue(row.getCell((short) index), maxLength);
	}

	/**
	 * 获取获取单元格值，如果为空就返回0
	 * 
	 * @param row
	 *            excel行对象
	 * @param index
	 *            excel单元格的位置
	 * @param maxLength单元格值最大长度
	 * @return 返回获取单元格值
	 */
	protected static String getCellValueReturnZero(Row row, int index,
			int maxLength) {
		String value = getCellValue(row.getCell((short) index), maxLength);
		return "".equals(value) ? "0" : value;
	}

	/**
	 * 获取获取单元格值
	 * 
	 * @param cell
	 *            单元格对象
	 * @param maxLength
	 *            单元格值最大长度
	 * @return
	 */
	protected static String getCellValue(Cell cell, int maxLength) {
		String value = "";
		if (cell != null) {
			switch (cell.getCellType()) {
			case Cell.CELL_TYPE_STRING:
				value = cell.getStringCellValue();
				break;
			case Cell.CELL_TYPE_NUMERIC:
				if (DateUtil.isCellDateFormatted(cell)) {
					DateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
					value = sdf.format(cell.getDateCellValue());
				} else {
					value = df.format(cell.getNumericCellValue());
				}
				break;
			case Cell.CELL_TYPE_BOOLEAN:
				value = String.valueOf(cell.getBooleanCellValue());
				break;
			}
		}
		value = value.trim();
		return value.substring(0, maxLength > value.length() ? value.length()
				: maxLength);
	}
	
	
	public void writeExcel(String sheetname,OutputStream output,String[]header,List<List<String>> values) throws IOException{
		Sheet sheet= wb.createSheet("sheetname");
		Row row = sheet.createRow(0);
		Cell cell = null;
		//创建表头
		for(int i=0;i<header.length;i++){
			cell = row.createCell(i);
			cell.setCellValue(header[i]);
		}
		for(int r=0;r<values.size();r++){
			row = sheet.createRow(r+1);
			for(int c=0;c<values.get(r).size();c++){
				cell = row.createCell(c);
				cell.setCellValue(values.get(r).get(c));
			}
		}
		output.flush();
		wb.write(output);
		output.close();
		
	}
	
	public static void main(String[] args) {
		ExcelParseScript e = new ExcelParseScript();
		String[] header = new String[]{"应聘者姓名","应聘者手机","职位名称","通知日期","预通知日期","录用日期","面试时间"," 状态","招聘网站","简历来源","简历","录入人","公司","部门","评价","备注","未面试原因"};
		try {
			FileOutputStream file = new FileOutputStream("1.xls");
			e.builExcel("xls");
			e.writeExcel("sheet", file,header,null);
		} catch (IOException e1) {
			e1.printStackTrace();
		}
	}
}
